<?php
//1、检索出学生最多的班级名称
	SELECT c.name, COUNT(s.id) AS total FROM class c INNER JOIN stu s on c.id = s.cid GROUP BY c.id ORDER BY total DESC LIMIT 1;

//2、检索出没有学生的班级名称
	SELECT c.name FROM class c LEFT JOIN stu s ON c.id = s.cid WHERE s.id IS NULL;

//3、检索出没有班级的学生名称
	SELECT s.name FROM class c RIGHT JOIN stu s ON c.id = s.cid WHERE c.id IS NULL;

//4、检索出女生最多的班级名称
	SELECT c.name, COUNT(s.id) AS total FROM class c INNER JOIN stu s ON c.id = s.cid WHERE sex = 2 GROUP BY c.id ORDER BY total DESC LIMIT 1;

//5、检索出没有女生的班级名称
	SELECT c.name, COUNT(s.id) AS total FROM class c LEFT JOIN stu s ON c.id = s.cid WHERE sex = 2 GROUP BY c.id ORDER BY total ASC LIMIT 1;

// 6、检索出张三都学了哪些课程
	SELECT s.name,l.name FROM stu s INNER JOIN stu_lesson sl ON s.id = sl.sid INNER JOIN lesson l ON sl.lid = l.id WHERE s.name = '张三';

// 7、检索出哪些课程没有同学学习
	SELECT l.name FROM stu_lesson sl RIGHT JOIN lesson l ON sl.lid = l.id WHERE sl.sid IS NULL;

// 8、检索出最多人学习的课程名称
	SELECT l.name, COUNT(l.id) AS total FROM stu_lesson sl LEFT JOIN lesson l ON sl.lid = l.id GROUP BY l.id ORDER BY total DESC LIMIT 1;

// 9、检索出最多女生学习的课程名称
	SELECT l.name,COUNT(l.id) AS total FROM stu s INNER JOIN stu_lesson sl ON s.id = sl.sid INNER JOIN lesson l ON sl.lid = l.id WHERE sex = 2 GROUP BY l.id ORDER BY total DESC LIMIT 1;
	
// 10、检索出与张三学习同样课程的学生名称
	SELECT s.name FROM stu s INNER JOIN stu_lesson sl ON s.id = sl.sid WHERE lid IN (SELECT l.id FROM stu s INNER JOIN stu_lesson sl ON s.id = sl.sid INNER JOIN lesson l on sl.lid = l.id WHERE s.name = '张三') GROUP BY s.name HAVING COUNT(s.name) = (SELECT COUNT(stu_lesson.lid) FROM stu INNER JOIN stu_lesson ON stu.id = stu_lesson.sid WHERE stu.name = '张三');
?>